package de.voegel.controller;

import java.io.FileNotFoundException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashSet;
import java.util.List;
import java.util.Vector;

import de.voegel.controller.db.PoolFactory;
import de.voegel.controller.fileReader.FileReader;
import de.voegel.model.DbString;
import de.voegel.model.SaveBird;
import de.voegel.model.entity.BirdEntity;
import de.voegel.model.entity.ChecklistEntity;
import de.voegel.model.entity.CompareBird;
import de.voegel.model.entity.PlaceEntity;
import de.voegel.model.entity.UserEntity;

public class DbController {

	private final MainControl mainControl;

	private String user = PoolFactory.USER;
	private String password = PoolFactory.PW;

	public DbController(MainControl mainControl) throws SQLException {
		this.mainControl = mainControl;
	}

	public void initDB() throws SQLException {
		try {
			String query = new FileReader().getResult();
			PreparedStatement stmtAuth = getConnection()
					.prepareStatement(query);

			stmtAuth.execute();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} finally {
			closeConnection();
		}
	}

	public void closeConnection() {
		try {
			PoolFactory.closeConnection();
		} catch (SQLException e) {
			// Waynes World:
			e.printStackTrace();
		}
	}

	public Connection getConnection() throws SQLException {
		return getConnection(user, password);
	}

	public Connection getConnection(String user, String pw) throws SQLException {
		return PoolFactory.getConnection(user, pw);
	}

	public boolean auth(String user, String password) throws SQLException {
		String selectAuth = "SELECT * FROM BENUTZER_VIEW WHERE EMAIL = ? AND PASSWORD = ?";
		PreparedStatement stmtAuth = getConnection().prepareStatement(
				selectAuth);
		stmtAuth.setString(1, user);
		stmtAuth.setString(2, password);

		boolean auth = false;
		ResultSet rs = stmtAuth.executeQuery();
		if (rs.next()) {
			auth = true;
		}

		mainControl.getLogger().info(
				String.format("Requesting Authentication for user %s", user));
		if (auth) {
			mainControl.getLogger()
					.info(String.format(
							"Authentication for user %s successful", user));
		} else {
			mainControl.getLogger().info(
					String.format("Authentication for user %s not successful",
							user));
		}
		return auth;
	}

	public HashSet<DbString> getContryFromDb() throws SQLException {
		HashSet<DbString> data = new HashSet<>();

		String selectAuth = "SELECT ID, LAND FROM ORT";
		PreparedStatement stmtAuth = getConnection().prepareStatement(
				selectAuth);

		ResultSet set = stmtAuth.executeQuery();
		while (set.next()) {
			data.add(new DbString(set.getLong("ID"), set.getString("LAND")));
		}

		return data;
	}

	public HashSet<DbString> getRegionFromDb() throws SQLException {
		HashSet<DbString> data = new HashSet<>();

		String selectAuth = "SELECT ID, NAME FROM ORT";
		PreparedStatement stmtAuth = getConnection().prepareStatement(
				selectAuth);

		ResultSet set = stmtAuth.executeQuery();
		while (set.next()) {
			data.add(new DbString(set.getLong("ID"), set.getString("NAME")));
		}

		return data;
	}

	public boolean userIsAdmin(String username) {
		ArrayList<DbString> data = new ArrayList<>();

		String selectAuth = "SELECT R.AS_INT, B.RECHT FROM BENUTZER B INNER JOIN ROLLE R ON B.RECHT = R.RECHT WHERE EMAIL = ?";
		try {
			PreparedStatement stmtAuth = getConnection().prepareStatement(
					selectAuth);
			stmtAuth.setString(1, username);
			ResultSet set = stmtAuth.executeQuery();
			if (set.next()) {
				String rolle = set.getString("RECHT");
				System.err.println("User mit Rolle:" + rolle);
				if (set.getInt("AS_INT") >= 10) {
					return true;
				} else {
					return false;
				}
			}
		} catch (SQLException e) {
			return false;
		}
		return true;
	}

	public HashSet<DbString> getOrtFromDb() throws SQLException {
		HashSet<DbString> data = new HashSet<>();

		String selectAuth = "SELECT ID, PROVINZ FROM ORT";
		PreparedStatement stmtAuth = getConnection().prepareStatement(
				selectAuth);

		ResultSet set = stmtAuth.executeQuery();
		while (set.next()) {
			data.add(new DbString(set.getLong("ID"), set.getString("PROVINZ")));
		}

		return data;
	}

	public HashSet<DbString> getZooRegionFromDb() throws SQLException {
		HashSet<DbString> data = new HashSet<>();

		String selectAuth = "SELECT ID, ZOOGEOGRAPHISCHEREGION FROM ORT";
		PreparedStatement stmtAuth = getConnection().prepareStatement(
				selectAuth);

		ResultSet set = stmtAuth.executeQuery();
		while (set.next()) {
			data.add(new DbString(set.getLong("ID"), set
					.getString("ZOOGEOGRAPHISCHEREGION")));
		}

		return data;
	}

	public Vector<BirdEntity> getAvailableBirds() throws SQLException {
		Vector<BirdEntity> data = new Vector<BirdEntity>();

		String selectAuth = "SELECT ID ,ENG_NAME, LAT_NAME, DT_NAME, KATEGORIE FROM ART WHERE KATEGORIE = 'species' AND dt_name IS NOT NULL";
		PreparedStatement stmtAuth = getConnection().prepareStatement(
				selectAuth);

		ResultSet set = stmtAuth.executeQuery();
		while (set.next()) {
			data.add(new BirdEntity(set.getInt("ID"), set.getString("DT_NAME"),
					set.getString("ENG_NAME"), set.getString("LAT_NAME"), set
							.getString("KATEGORIE")));
		}
		return data;
	}

	public Vector<BirdEntity> getAvailableSubspecies(String bird)
			throws SQLException {
		Vector<BirdEntity> data = new Vector<BirdEntity>();

		PreparedStatement stmt = getConnection()
				.prepareStatement(
						"SELECT ID ,ENG_NAME, LAT_NAME, DT_NAME, KATEGORIE FROM ART WHERE KATEGORIE = 'subspecies' AND LAT_NAME LIKE ?");
		stmt.setString(1, bird + "%");

		ResultSet set = stmt.executeQuery();
		while (set.next()) {
			System.out.println(set.getInt("ID"));
			data.add(new BirdEntity(set.getInt("ID"), set.getString("DT_NAME"),
					set.getString("ENG_NAME"), set.getString("LAT_NAME"), set
							.getString("KATEGORIE")));
		}
		return data;
	}

	public int addNewOrt(String ort, String land, String region, String gebiet)
			throws SQLException {
		PreparedStatement stmtAuth = getConnection().prepareStatement(
				"SELECT COUNT(*) FROM ORT");
		ResultSet resultSet = stmtAuth.executeQuery();
		stmtAuth.close();

		String selectAuth = "INSERT INTO ORT(ORT.name, ort.provinz, ort.land, ort.zoogeographischeregion) VALUES (?, ?, ?, ?)";
		stmtAuth = getConnection().prepareStatement(selectAuth);
		stmtAuth.setString(1, ort);
		stmtAuth.setString(2, region);
		stmtAuth.setString(3, land);
		stmtAuth.setString(4, gebiet);

		return stmtAuth.executeUpdate();
	}

	public List<ChecklistEntity> getChecklist(String country, String email)
			throws SQLException {
		List<ChecklistEntity> data = new ArrayList<ChecklistEntity>();

		int modus = 0;
		if (email == null) {
			modus = 1;
		} else {
			modus = 2;
		}
		if (country.equalsIgnoreCase("< alle >")) {
			modus = 0;
		}

		String select = "";
		PreparedStatement stmt = null;
		if (modus == 0) {
			// alle selektieren
			select = "SELECT b.id, startzeitpunkt, o.land, a.lat_name FROM beobachtung b INNER JOIN art_beobachtung ab ON ab.beobachtung_id = b.id INNER JOIN art a ON a.id = ab.art_id INNER JOIN ort o ON b.ort_id = o.id";
			stmt = getConnection().prepareStatement(select);
		} else if (modus == 1) {
			select = "SELECT b.id, startzeitpunkt, o.land, a.lat_name FROM beobachtung b INNER JOIN art_beobachtung ab ON ab.beobachtung_id = b.id INNER JOIN art a ON a.id = ab.art_id INNER JOIN ort o ON b.ort_id = o.id WHERE o.land = ?";
			stmt = getConnection().prepareStatement(select);
			stmt.setString(1, country);
		} else if (modus == 2) {
			select = "SELECT b.id, startzeitpunkt, o.land, a.lat_name FROM beobachtung b INNER JOIN art_beobachtung ab ON ab.beobachtung_id = b.id INNER JOIN art a ON a.id = ab.art_id INNER JOIN ort o ON b.ort_id = o.id WHERE o.land = ?  AND b.email = ?";
			stmt = getConnection().prepareStatement(select);
			stmt.setString(1, country);
			stmt.setString(2, email);
		}

		ResultSet set = stmt.executeQuery();

		while (set.next()) {
			data.add(new ChecklistEntity(set.getInt("id"), set
					.getTimestamp("startzeitpunkt"), set.getString("land"), set
					.getString("lat_name")));
		}

		return data;
	}

	public String addNewBeobachtung(ArrayList<SaveBird> saveBirds,
			java.sql.Timestamp start, java.sql.Timestamp stop,
			String bemerkung, String email, String ort, String land,
			String region, String gebiet) throws SQLException {
		Integer ortId = 0;
		String selectAuth = "";
		if (ort != null && region != null) {
			selectAuth = "SELECT ID FROM ORT WHERE ZOOGEOGRAPHISCHEREGION = ? AND LAND = ? AND PROVINZ = ? AND NAME = ?";
		} else {
			selectAuth = "SELECT ID FROM ORT WHERE ZOOGEOGRAPHISCHEREGION = ? AND LAND = ?";
		}
		closeConnection();
		PreparedStatement stmtAuth = getConnection().prepareStatement(
				selectAuth);
		stmtAuth.setString(1, gebiet);
		stmtAuth.setString(2, land);
		if (ort != null && region != null) {
			stmtAuth.setString(3, region);
			stmtAuth.setString(4, ort);
		}

		ResultSet set = stmtAuth.executeQuery();
		while (set.next()) {
			ortId = set.getInt("ID");
		}

		PreparedStatement stmt = getConnection()
				.prepareStatement(
						"INSERT INTO BEOBACHTUNG(startzeitpunkt, endzeitpunkt, kommentar, ort_id, email) VALUES (?, ?, ?, ?, ?)");

		stmt.setTimestamp(1, start);
		stmt.setTimestamp(2, stop);
		stmt.setString(3, bemerkung);
		stmt.setInt(4, ortId);
		stmt.setString(5, email);

		Integer lastId = null;

		stmt.executeUpdate();

		stmt = getConnection().prepareStatement(
				"SELECT ID from BEOBACHTUNG ORDER BY ID DESC");
		set = stmt.executeQuery();
		while (set.next()) {
			if (lastId == null) {
				lastId = set.getInt("ID");
			}
		}

		String tick = "";

		for (SaveBird saveBird : saveBirds) {
			boolean next = true;
			stmtAuth = getConnection()
					.prepareStatement(
							"INSERT INTO ART_BEOBACHTUNG(BEOBACHTUNG_ID, ART_ID) VALUES (?, ?)");
			stmtAuth.setInt(1, lastId);
			stmtAuth.setInt(2, saveBird.getId());

			stmtAuth.executeUpdate();

			// lifer
			stmt = getConnection()
					.prepareStatement(
							"SELECT COUNT(*) AS c FROM art a INNER JOIN art_beobachtung ab ON a.id = ab.art_id INNER JOIN beobachtung b ON ab.beobachtung_id = b.id WHERE b.email = ? AND a.id = ?");
			stmt.setString(1, email);
			stmt.setInt(2, saveBird.getId());
			set = stmt.executeQuery();
			set.next();
			if (set.getInt("c") == 1) {
				tick += " " + saveBird.toString() + "Lifer ";
				next = false;
			}

			if (next) {
				// tick
				stmt = getConnection()
						.prepareStatement(
								"SELECT COUNT(*) AS c FROM art a INNER JOIN art_beobachtung ab ON a.id = ab.art_id INNER JOIN beobachtung b ON ab.beobachtung_id = b.id WHERE b.email = ? AND a.id = ? AND b.ort_id = ?");
				stmt.setString(1, email);
				stmt.setInt(2, saveBird.getId());
				stmt.setInt(3, ortId);
				set = stmt.executeQuery();
				set.next();
				if (set.getInt("c") == 1) {
					tick += " " + saveBird.toString() + "[Tick] ";
				} else {
					tick += " ";
				}
			}
		}
		return tick;
	}

	public void setCredentials(String dbUsername, String dbPasswordText) {
		this.user = dbUsername;
		this.password = dbPasswordText;
	}

	public List<UserEntity> getAvailableUser() throws SQLException {
		List<UserEntity> data = new ArrayList<UserEntity>();

		String selectAuth = "SELECT EMAIL FROM BENUTZER";
		PreparedStatement stmtAuth = getConnection().prepareStatement(
				selectAuth);

		ResultSet set = stmtAuth.executeQuery();
		while (set.next()) {
			data.add(new UserEntity(set.getString("EMAIL")));
		}
		return data;
	}

	public List<PlaceEntity> getAvailablePlaces() throws SQLException {
		List<PlaceEntity> data = new ArrayList<PlaceEntity>();

		String selectAuth = "SELECT ID, NAME,PROVINZ,LAND,ZOOGEOGRAPHISCHEREGION FROM ORT";
		PreparedStatement stmtAuth = getConnection().prepareStatement(
				selectAuth);

		ResultSet set = stmtAuth.executeQuery();
		while (set.next()) {
			data.add(new PlaceEntity(set.getLong("ID"), noNull(set
					.getString("NAME"))
					+ "/"
					+ noNull(set.getString("PROVINZ"))
					+ "/"
					+ noNull(set.getString("LAND"))
					+ "/"
					+ noNull(set.getString("ZOOGEOGRAPHISCHEREGION"))));
		}
		return data;
	}

	private String noNull(String s) {
		if (s == null) {
			s = "";
		}
		return s;
	}

	public HashSet<DbString> getAvailableCountry(String selString)
			throws SQLException {
		HashSet<DbString> data = new HashSet<>();

		String selectAuth = "SELECT ID, LAND FROM ORT WHERE ZOOGEOGRAPHISCHEREGION = ?";
		PreparedStatement stmtAuth = getConnection().prepareStatement(
				selectAuth);

		stmtAuth.setString(1, selString);

		ResultSet set = stmtAuth.executeQuery();
		while (set.next()) {
			data.add(new DbString(set.getLong("ID"), set.getString("LAND")));
		}

		return data;
	}

	public HashSet<DbString> getAvailableOrt(String selString)
			throws SQLException {
		HashSet<DbString> data = new HashSet<>();

		String selectAuth = "SELECT ID, PROVINZ FROM ORT WHERE LAND = ?";
		PreparedStatement stmtAuth = getConnection().prepareStatement(
				selectAuth);

		stmtAuth.setString(1, selString);

		ResultSet set = stmtAuth.executeQuery();
		while (set.next()) {
			data.add(new DbString(set.getLong("ID"), set.getString("PROVINZ")));
		}

		return data;
	}

	public HashSet<DbString> getAvailableName(String selString)
			throws SQLException {
		HashSet<DbString> data = new HashSet<>();

		String selectAuth = "SELECT ID, NAME FROM ORT WHERE PROVINZ = ?";
		PreparedStatement stmtAuth = getConnection().prepareStatement(
				selectAuth);

		stmtAuth.setString(1, selString);

		ResultSet set = stmtAuth.executeQuery();
		while (set.next()) {
			data.add(new DbString(set.getLong("ID"), set.getString("NAME")));
		}

		return data;
	}

	public HashSet<CompareBird> getBirdsDe() throws SQLException {
		HashSet<CompareBird> birds = new HashSet<>();
		String selectAuth = "SELECT DE_DEUTSCH, DE_ENGLISCH, DE_LATEIN FROM MERLIN.BIRDS_DE";
		PreparedStatement stmtAuth = getConnection().prepareStatement(
				selectAuth);
		ResultSet set = stmtAuth.executeQuery();
		while (set.next()) {
			birds.add(new CompareBird(set.getString("DE_DEUTSCH"), set
					.getString("DE_LATEIN")));
		}

		return birds;
	}

	public HashSet<CompareBird> getGermanBirds(String user) throws SQLException {
		HashSet<CompareBird> birds = new HashSet<>();
		String selectAuth = "SELECT a.lat_name AS lat, a.dt_name AS deutsch FROM art a INNER JOIN art_beobachtung ab ON a.id = ab.art_id INNER JOIN beobachtung b ON ab.beobachtung_id = b.id INNER JOIN ort o ON o.id = b.ort_id WHERE b.email = ? AND o.land = ?";
		PreparedStatement stmtAuth = getConnection().prepareStatement(
				selectAuth);
		stmtAuth.setString(1, user);
		stmtAuth.setString(2, "Deutschland");
		ResultSet set = stmtAuth.executeQuery();
		while (set.next()) {
			birds.add(new CompareBird(set.getString("deutsch"), set
					.getString("lat")));
		}

		return birds;
	}

	public void addBird(String latName, String dName, String engName,
			String spec) throws SQLException {
		String selectAuth = "SELECT ID AS ID FROM ART ORDER BY ID DESC";
		PreparedStatement stmtAuth = getConnection().prepareStatement(
				selectAuth);
		ResultSet set = stmtAuth.executeQuery();

		Integer id = 0;
		if (set.next()) {
			id = set.getInt("ID");
		}
		id++;

		selectAuth = "INSERT INTO ART(ID, ENG_NAME, LAT_NAME, DT_NAME, KATEGORIE) VALUES(?,?,?,?,?)";
		stmtAuth = getConnection().prepareStatement(selectAuth);

		stmtAuth.setInt(1, id);
		stmtAuth.setString(2, engName);
		stmtAuth.setString(3, latName);
		stmtAuth.setString(4, dName);
		stmtAuth.setString(5, spec);

		stmtAuth.executeUpdate();
	}

}
